﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;

namespace Agro.Library {

    public static class NPOIExtension {

        public static void InsertRow(this ISheet sheet, int startRow, int insertRowCount, IRow soureRow) {
            sheet.ShiftRows(startRow, sheet.LastRowNum, insertRowCount, true, false);
            sheet.GetRow(startRow).Height = soureRow.Height;
            for (int i = startRow; i < startRow + insertRowCount - 1; i++) {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;
                targetRow = sheet.CreateRow(i + 1);
                targetRow.Height = soureRow.Height;
                for (int m = soureRow.FirstCellNum; m < soureRow.LastCellNum; m++) {
                    sourceCell = soureRow.GetCell(m);
                    if (sourceCell == null)
                        continue;
                    targetCell = targetRow.CreateCell(m);
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);
                }
            }
            IRow firstTargetRow = sheet.GetRow(startRow);
            ICell firstSourceCell = null;
            ICell firstTargetCell = null;
            for (int m = soureRow.FirstCellNum; m < soureRow.LastCellNum; m++) {
                firstSourceCell = soureRow.GetCell(m);
                if (firstSourceCell == null)
                    continue;
                firstTargetCell = firstTargetRow.CreateCell(m);
                firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                firstTargetCell.SetCellType(firstSourceCell.CellType);
            }
        }
    }

    /// <summary>
    /// NPOI操作类
    /// </summary>
    public static class NPOIHelper {

        /// <summary>
        /// 把Excel写入流
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="stream"></param>
        public static void WriteExcelToStream(DataTable dataTable, Stream stream) {
            WriteExcelToStream(dataTable, stream, null);
        }

        /// <summary>
        /// 把Excel写入流
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="stream"></param>
        /// <param name="columnWiths">宽度设置</param>
        public static void WriteExcelToStream(DataTable dataTable, Stream stream, params int[] columnWiths) {
            HSSFWorkbook workbook = GetDataWorkbook(dataTable, columnWiths);
            workbook.Write(stream);
        }

        /// <summary>
        /// 把Excel写入文件
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        public static void WriteExcelToFile(DataTable dataTable, string fileName) {
            WriteExcelToFile(dataTable, fileName, null);
        }

        /// <summary>
        /// 把Excel写入文件
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        /// <param name="columnWiths">宽度设置</param>
        public static void WriteExcelToFile(DataTable dataTable, string fileName, params int[] columnWiths) {
            HSSFWorkbook workbook = GetDataWorkbook(dataTable, columnWiths);
            using (FileStream fs = new FileStream(fileName, FileMode.CreateNew)) {
                workbook.Write(fs);
            }
        }

        /// <summary>
        /// 是否是数值类型
        /// </summary>
        /// <param name="sourceType"></param>
        /// <returns></returns>
        static bool IsNumberType(Type sourceType) {
            return sourceType == typeof(decimal) ||
                sourceType == typeof(decimal?) ||
                sourceType == typeof(int) ||
                sourceType == typeof(int?) ||
                sourceType == typeof(double) ||
                sourceType == typeof(double?) ||
                sourceType == typeof(float) ||
                sourceType == typeof(float?);
        }

        static HSSFWorkbook GetDataWorkbook(DataTable dataTable) {
            return GetDataWorkbook(dataTable, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="columnWiths">宽度设置</param>
        /// <returns></returns>
        static HSSFWorkbook GetDataWorkbook(DataTable dataTable, params int[] columnWiths) {
            if (dataTable == null) {
                throw new ArgumentNullException("dataTable");
            }
            if (dataTable.Rows.Count > 65535 || dataTable.Columns.Count > 256)
                throw new OutOfMemoryException("dataTable 行或列数量超出允许的范围");
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            var sheet = hssfworkbook.CreateSheet("Sheet1");
            if (columnWiths != null && columnWiths.Length > 0) {
                for (int i = 0; i < columnWiths.Length; i++) {
                    sheet.SetColumnWidth(i, columnWiths[i] * 255);
                }
            }
            var row = sheet.CreateRow(0);
            row.Height = 15 * 20;//默认行高15;
            sheet.CreateFreezePane(0, 1, 0, 1);//冻结首行
            //输出表头
            for (int i = 0; i < dataTable.Columns.Count; i++) {
                var cell = row.CreateCell(i);
                cell.SetCellValue(dataTable.Columns[i].ColumnName);
            }
            for (int i = 0; i < dataTable.Rows.Count; i++) {
                row = sheet.CreateRow(i + 1);
                row.Height = 15 * 20;//默认行高15;
                for (int j = 0; j < dataTable.Columns.Count; j++) {
                    var cell = row.CreateCell(j);
                    if (IsNumberType(dataTable.Columns[j].DataType)) {
                        if (dataTable.Rows[i][j] == DBNull.Value)
                            cell.SetCellValue("");
                        else
                            cell.SetCellValue(Convert.ToDouble(dataTable.Rows[i][j]));
                    }
                    else
                        cell.SetCellValue(Convert.ToString(dataTable.Rows[i][j]));
                }
            }
            return hssfworkbook;
        }
    }
}
